package com.softer.chengxin.score.dao;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Gong Zhiyang
 * @date 2019/2/15
 */
@Mapper
@Repository
public interface NewTypeDao {
    /**
     * 参加项目经理培训比例100%
     *
     * @return 企业Id集合
     */
    @Select("SELECT a.corp_id as id FROM (SELECT a.corp_id,a.num FROM(SELECT COUNT(*) as num,corp_id FROM company_train WHERE subject_type = 1 AND state = 2" +
            " GROUP BY corp_id)a JOIN (SELECT p.company_id,COUNT(*) as num FROM company_person p JOIN company_project cp on p.project_id =cp.id WHERE p.duties = 16" +
            " AND cp.state=2 AND p.state=1 GROUP BY p.company_id)b ON a.corp_id=b.company_id WHERE a.num=b.num)a JOIN (SELECT p.company_id,COUNT(*) as num " +
            " FROM company_person p JOIN company_train t ON p.id=t.person_id WHERE p.duties=16 AND p.state=1 GROUP BY p.company_id)b on a.corp_id=b.company_id WHERE a.num=b.num")
    List<Integer> getOrganizationList();

    /**
     * 获取所有参加项目经理培训的企业
     *
     * @return 企业ID结合
     */
    @Select("SELECT corp_id FROM company_train WHERE state=2 and subject_type=1 GROUP BY corp_id")
    List<Integer> getCorpList();

    /**
     * 根据企业ID查询当前企业所有在职的项目经理ID集合
     *
     * @param id 企业ID
     * @return 项目经理ID集合
     */
    @Select("SELECT p.id FROM company_person p JOIN company_project cp ON p.project_id = cp.id" +
            " WHERE p.duties = 16 AND cp.state = 2 AND p.state = 1 AND p.company_id=#{id}")
    List<Integer> getPersonId(@Param("id") Integer id);

    /**
     * 查询企业培训的项目经理的ID集合
     *
     * @param id 企业ID
     * @return 项目经理的ID集合
     */
    @Select("SELECT person_id FROM company_train WHERE state=2 and subject_type=1 AND corp_id=#{id}")
    List<Integer> getTrainPersonId(@Param("id") Integer id);

    /**
     * 查询企业下审核通过的项目的列表
     *
     * @param id 企业id
     * @return 项目ID结合
     */
    @Select("SELECT id FROM company_project WHERE state=2 AND company_id=#{id}")
    List<Integer> getProjectList(@Param("id") Integer id);

    /**
     * 查询每个项目下项目经理集合
     *
     * @param id 项目ID
     * @return 项目经理ID集合
     */
    @Select("SELECT `id` FROM company_person WHERE project_id=#{id} AND duties=16")
    List<Integer> getPersonIdList(@Param("id") Integer id);

    /**
     * 查询项目经理是否参加项目经理培训
     *
     * @param id 项目经理ID
     * @return 培训ID
     */
    @Select("SELECT id FROM company_train WHERE state=2 and subject_type=1 AND person_id=#{id}")
    Integer getTrainId(@Param("id") Integer id);

    /**
     * 查询所有需要更新分数的企业
     *
     * @param year 年份
     * @return 企业ID集合
     */
    @Select("SELECT company_id FROM score_composite WHERE `year`=#{year} AND all_score>0")
    List<Integer> getAllCorpId(@Param("year") String year);

    /**
     * 查询所有企业ID
     *
     * @return 企业ID列表
     */
    @Select("SELECT id FROM company_base_info")
    List<Integer> getCoreIdList();

}
